#include <sqlite3.h>
#include <stdio.h>

#define DATABASE_NAME "student.db"
#define STRLEN 50

static int callback(void *NotUsed, int argc, char **argv, char **azColName) {
    int i;
    printf("查询结果:\n");
    for(i = 0; i < argc; i++) {
        printf("%s: %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    printf("\n");

    return 0;
}

int main(int argc, char* argv[]) {
	sqlite3 *db;
	char *zErrMsg = 0;
	int rc;
	/*打开数据库*/
	rc = sqlite3_open(DATABASE_NAME, &db);
	if (rc) {
		fprintf(stderr, "无法打开数据库: %s\n", sqlite3_errmsg(db));
		sqlite3_close(db);
		return 1;
	}

	char *sql_query, name[STRLEN], phone[STRLEN], grade[STRLEN];
	int id, opt;

	/*创建表的SQL语句*/
	char *sql = "CREATE TABLE IF NOT EXISTS student ("
		"id INTEGER PRIMARY KEY AUTOINCREMENT,"
		"name TEXT NOT NULL,"
		"phone TEXT,"
		"grade TEXT"
		");";

	/*执行SQL语句*/
	rc = sqlite3_exec(db, sql, 0, 0, &zErrMsg);
	if (rc != SQLITE_OK) {
		fprintf(stderr, "SQL error: %s\n", zErrMsg);
		sqlite3_free(zErrMsg);
	} else {
		printf("Table created successfully\n");
	}

	while(1) {
		printf("请输入要执行的操作：\n");
		printf("1. 添加学生\n");
		printf("2. 查找学生\n");
		printf("3. 更新学生信息\n");
		printf("4. 删除学生\n");
		printf("5. 退出程序\n");
		/*获取用户命令*/
		while( !scanf("%d", &opt) ) getchar();

		switch(opt) {
		case 1:
			// 添加学生
			printf("请输入姓名：\n");
			scanf("%s", name); 
			getchar();
			printf("请输入电话号码：\n");
			scanf("%s", phone);
			getchar();
			printf("请输入所在班级：\n");
			scanf("%s", grade);
			getchar();
			printf("您输入的姓名是: %s\t电话号码是: %s\n班级是: %s\n", name, phone, grade);
			printf("请输入[y]确认 \n");
			if('y' != getchar()) {
				while(getchar() != '\n');
				printf("添加学生失败 [已取消]\n");
				continue;
			}
			sql_query = sqlite3_mprintf("INSERT INTO student "
					"(name, phone, grade) VALUES('%s', '%s', '%s')", 
					name, phone, grade);
			rc = sqlite3_exec(db, sql_query, NULL, NULL, &zErrMsg);
			if(rc != SQLITE_OK) {
				printf("添加学生失败 [%s]\n", zErrMsg);
				sqlite3_free(zErrMsg);
			} else {
				printf("添加学生成功！\n");
			}
			sqlite3_free(sql_query);
			break;	
		case 2:
			//查找学生
			printf("请输入要查找的姓名或部分姓名：\n");
			scanf("%s", name);
			getchar();

			sql_query = sqlite3_mprintf("SELECT * FROM student WHERE name LIKE '%s'", name);
			rc = sqlite3_exec(db, sql_query, callback, NULL, &zErrMsg);
			if(rc != SQLITE_OK) {
				printf("查询学生失败: %s\n", zErrMsg);
				sqlite3_free(zErrMsg);
			}
			break;
		case 3:
			// 更新学生
			printf("请输入要更新的学生ID：\n");
			while( !scanf("%d", &id) ) getchar();
			printf("请输入新的姓名：\n");
			scanf("%s", name);
			getchar();
			printf("请输入新的电话号码：\n");
			scanf("%s", phone);
			getchar();
			printf("请输入新的班级：\n");
			scanf("%s", grade);
			getchar();

			sql_query = sqlite3_mprintf("UPDATE student SET name='%s', phone='%s', grade='%s' WHERE id=%d", 
					name, phone, grade, id);
			rc = sqlite3_exec(db, sql_query, NULL, NULL, &zErrMsg);
			if(rc != SQLITE_OK) {
				printf("更新学生失败: %s\n", zErrMsg);
				sqlite3_free(zErrMsg);
			} else {
				printf("更新学生成功！\n");
			}
			break;
		case 4:
			// 删除学生
			printf("请输入要删除的学生ID：\n");
			while( !scanf("%d", &opt) ) getchar();

			sql_query = sqlite3_mprintf("DELETE FROM student WHERE id=%d", opt);
			rc = sqlite3_exec(db, sql_query, NULL, NULL, &zErrMsg);
			if(rc != SQLITE_OK) {
				printf("删除学生失败: %s\n", zErrMsg);
				sqlite3_free(zErrMsg);
			} else {
				printf("删除成功！\n");
			}
			break;
		case 5:
			sqlite3_close(db);
			return 1;
			break;
		default:
			printf("无效的选项，请重新输入！\n");
			break;
		}
	}
	/*关闭数据库文件*/
	sqlite3_close(db);
	return 0;
}
